# -*- coding: utf-8 -*-
import requests,time,pymysql
import urllib.request,urllib.error
import os,re,IPy,xlrd,xlwt
from multiprocessing import Pool

def CDN(ip):
    try:
        for x in DDIP:
            if ip in IPy.IP(x[0]):
                print(ip, "已存在：", x[1])
                return "已存在：" + x[1]
        print(ip, "不存在")
        return "不存在"
    except:
        print(ip, "不存在")
        return "不存在"


def read_excel(file):#读取调度IP，生成列表
    DDIP=xlrd.open_workbook(file)
    #print(DDIP.sheet_names())
    DDIP_sh= DDIP.sheet_by_index(0)
    row_num=DDIP_sh.nrows
    #print(row_num)
    DDIP_list=[]
    for x in range(row_num):
        #print(DDIP_sh.row_values(x))
        DDIP_list.append(DDIP_sh.row_values(x))
    #print(DDIP_list)
    #DDIP_list.pop(0)
    return  DDIP_list

def LocalDNS(host):#本地使用常用dns进行解析
    ip=[]
    for server in ["","114.114.114.114","223.5.5.5","218.108.248.200"]:
        NSr=os.popen('nslookup %s %s' % (host,server)).read()
        try:
            NSr = NSr[NSr.find("Name:"):]
            iplist = re.findall("\d+\.\d+\.\d+\.\d+", NSr)
            ip=ip+iplist
        except:
            continue
    return ip

def DNSserver(host):#获取站长工具的dns服务器列表
    #host = "www.163.com"
    URL = "http://tool.chinaz.com/dns/?type=1&host=%s&ip=" % host
    r = requests.get(url=URL)
    r.encoding = "utf-8"
    NS = str(r.text)
    serverList = re.findall('id\:\d+\,ip:"[^"]+"', NS)
    return  serverList

def DNSresv(id, ip,host):#使用站长工具服务器对域名进行异地解析
    PAM = {"t": "dns",
            "server": ip,
            "id": id,
            "callback": "jQuery111303087353741212444_1505459898368"
            }
    DATA = {
            "host":host,
            "type":"1",
            "total":"19",
            "process":"0",
            "right":"0"
            }
    URL="http://tool.chinaz.com/AjaxSeo.aspx"
    try:
        r = requests.post(url=URL, params=PAM, data=DATA)
        r.encoding = "utf-8"
        anwser = re.findall('\d+\.\d+\.\d+\.\d+', r.text)
    except:
        anwser=[]
    return anwser
def updateDB(IP,exist="不存在"):#[ip,cp,domain]
    IP[2]=IP[2].strip().strip("\n")
    db=pymysql.connect("113.215.1.161","root","root","mydb",charset="utf8" )# 打开数据库连接
    cursor = db.cursor()# 使用cursor()方法获取操作游标
    #查询
    sqlFind='SELECT * FROM IPcheckTab WHERE IP="%s"' % IP[0]
    #更新
    sqlupdate='UPDATE IPcheckTab SET CP="%s",domain="%s",exist="%s" WHERE IP="%s" ' % (IP[1],IP[2],exist,IP[0])
    #插入
    sqlInsert='INSERT INTO IPcheckTab(IP,CP,domain,exist) VALUES ("%s","%s","%s","%s")' % (IP[0],IP[1],IP[2],exist)
    print(sqlFind)
    Find=cursor.execute(sqlFind)
    print("找到%s条记录" % Find)
    if Find==0:#如果库中无次记录
        try:
            cursor.execute(sqlInsert)  # 插入记录
            print(sqlInsert,"插入成功")
        except:
            print(sqlInsert,"插入失败")
            db.close()
            return 0
    else:
        try:
            cursor.execute(sqlupdate)#更新记录
            print(sqlupdate,"更新成功")
        except:
            print(sqlupdate, "更新失败")
            db.close()
            return 0
    db.close()

def readDB(DBname):
    db = pymysql.connect("113.215.1.161", "root", "root", "mydb", charset="utf8")  # 打开数据库连接
    cursor = db.cursor()  # 使用cursor()方法获取操作游标
    sql = "SELECT * FROM %s" % DBname
    cursor.execute(sql)
    results = cursor.fetchall()
    db.close()
    return results

def mail():
    db = pymysql.connect("113.215.1.161", "root", "root", "mydb", charset="utf8")  # 打开数据库连接
    cursor = db.cursor()  # 使用cursor()方法获取操作游标
    sql = "SELECT * FROM IPcheckTab WHERE CP LIKE  '腾讯%'  AND exist= '不存在'"
    find=cursor.execute(sql)
    results = cursor.fetchall()
    with open('/tmp/tencentmail.txt','w') as fmail:
        for x in results:
            fmail.write("%s\n" % (x[0],))
    db.close()
    if find!=0:
        os.system('/bin/mail -s "腾讯调度新增"  ganlian@watone.com.cn   < /tmp/tencentmail.txt')


def main(hostANDcp):
    host=hostANDcp[1]
    CP=hostANDcp[0]
    server = DNSserver(host)#获取站长工具的服务器接口
    AnserList = []
    anwser1=[]
    print("正在域名解析",host,CP)
    for x in server:
        time.sleep(2)
        x = x.split(",")
        id = x[0].split(":")[1]
        ip = x[1].split(":")[1].strip('"')
        anwser=DNSresv(id, ip, host)
        anwser1 = anwser1+anwser#用站长之家的工具去解析域名
    print(host,"站长之家",anwser1)
    anwser2=LocalDNS(host)
    print(host,"LDNS",anwser2)
    for i in list(set(anwser1+anwser2)):
            AnserList.append([i,CP,host])
    return AnserList


if __name__ == '__main__':
    DDIP=readDB("DDIPtab")#加载调度IP
    print("调度表加载完毕")
    hostlist=list(readDB("DDIP_Domain"))+list(readDB("DDIP_Domain_tencent"))#加载调度控制域名，DDIP_Domain为自己收集，DDIP_Domain_tencent为腾讯api获取
    print("加载调度控制域名")
    #print(hostlist)
    p = Pool(10)#开10个进程
    DD_Dict={}
    res=p.map(main,hostlist)#用站长之家的接口检测
    for i in res:
        for j in i:
            print(j)
            DD_Dict[j[0]]=j
    for IP in DD_Dict:
        updateDB(DD_Dict[IP])
    p.close()
    p.join()
    IPcheckTab=readDB("IPcheckTab")
    for x in IPcheckTab:
        x=list(x)
        exist=CDN(x[0])
        x.pop()
        updateDB(x,exist)
    mail()








